/**
 * 
 */
package com.sysmanage.etl.controls;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.annotation.Resource;

import net.sf.json.JSONArray;

import org.apache.commons.io.FileUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.struts2.json.annotations.JSON;
import org.springframework.beans.BeanUtils;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Controller;

import com.sysmanage.common.tools.util.AppParamMemoryUtils;
import com.sysmanage.etl.model.ETL_Column;
import com.sysmanage.etl.model.ETL_Excel;
import com.sysmanage.etl.model.ETL_Rule;
import com.sysmanage.etl.service.IColumnService;
import com.sysmanage.etl.service.IErrorLogService;
import com.sysmanage.etl.service.IExcelService;
import com.sysmanage.etl.service.IRuleService;
import com.sysmanage.etl.utils.DateUtils;
import com.sysmanage.etl.utils.ETLUtil;
import com.sysmanage.etl.utils.ExcelUtils;
import com.sysmanage.etl.utils.PropertiesUtils;
import com.sysmanage.etl.vo.ExcelVO;
import com.sysmanage.etl.vo.ShopVo;
import com.sysmanage.permissions.ConstCode;

/**
 * @author Yu Jou 2014年7月10日
 */
@Scope("prototype")
@Controller(value = "excelTg")
@SuppressWarnings("unused")
public class ExcelTg extends BaseETLTg {
	private static final long serialVersionUID = -9092779254164333444L;

	@Resource
	private IExcelService excelService;
	@Resource
	private IColumnService columnService;
	@Resource
	private IRuleService ruleService;
	@Resource
	private IErrorLogService errorLogService;

	private List<ExcelVO> datas = new ArrayList<ExcelVO>(0);

	// 文件上传相关
	private File excel;
	private String excelContentType;
	private String excelFileName;

	private Integer rowIndex = 0;
	private String excelName;
	private Integer excelId;
	// 操作提示
	private Boolean success = false;
	private String message;

	public String main() {
		List<ETL_Excel> excels = excelService.findList("from ETL_Excel where shop_id = " + this.getShopId() + " order by excel_uploadTime desc");
		if(excels != null && excels.size() > 0){
			this.rowIndex = excels.get(0).getExcel_rowIndex();
		}
		return "main";
	}

	public String data() {
		return "dataJSON";
	}

	public void prepareData() {
		this.datas.clear();
		List<ETL_Excel> excels = null;
		if(ETLUtil.isAdmin(this.getShopId())){
			excels = excelService.findList("from ETL_Excel order by shop_id , excel_uploadTime desc", page, rows);
			this.total = excelService.findCountBySql("from ETL_Excel");
		}else{
			excels = excelService.findList("from ETL_Excel  where shop_id = " + this.getShopId() + " order by excel_uploadTime desc", page, rows);
			this.total = excelService.findCountBySql("from ETL_Excel where shop_id = " + this.getShopId() + " group by shop_id");
		}
		for (ETL_Excel excel : excels) {
			AppParamMemoryUtils dictionaryUtils = AppParamMemoryUtils.getInstance();
			String shopName = ETLUtil.convertID2Name(this.getShopId());
			ExcelVO vo = new ExcelVO();
			BeanUtils.copyProperties(excel, vo);
			int errors = errorLogService.findCountBySql("from ETL_ErrorLog where excel.excel_id = " + excel.getExcel_id() + " and status = 0");
			if (errors > 0) {
				vo.setErrors(true);
			}
			vo.setShopName(shopName);
			this.datas.add(vo);
		}
		
		if(ETLUtil.isAdmin(this.getShopId())){
			this.total = excelService.findCountBySql("from ETL_Excel");
		}else{
			this.total = excelService.findCountBySql("from ETL_Excel where shop_id = " + this.getShopId() + " group by shop_id");
		}
		
		
	}
	
	
	public void shops() {
		List<Integer> shopIds = null;
		if(ETLUtil.isAdmin(this.getShopId())){
			shopIds = excelService.shops("select shop_id from ETL_Excel group by shop_id");
		}else{
			shopIds = excelService.shops("select shop_id from ETL_Excel where shop_id = " + this.getShopId() + " group by shop_id");
		}
		
		List<ShopVo> vos = new ArrayList<ShopVo>();
		ShopVo vo;
		for (Integer id : shopIds) {
			vo = new ShopVo();
			vo.setShopName(ETLUtil.convertID2Name(this.getShopId()));
			vo.setShop_id(id);
			vo.setIsMapped(ruleService.hasRule(id));
			vos.add( vo );
		}
		
		JSONArray json = JSONArray.fromObject(vos);
		this.outJsonPlainString(response, json.toString());
	}
	

	// 下载文件
	public String download() {
		return "excelStream";
	}

	public InputStream getExcelFile() throws Exception {
		ETL_Excel excel = excelService.findById(excelId);
		this.excelFileName = excel.getExcel_name();
		InputStream inputStream = new FileInputStream(excel.getExcel_filePath());
		return inputStream;
	}

	// 上传Execel文件
	public String upload() {
		List<ETL_Rule> rules = ruleService.findList("from ETL_Rule r where r.shop_id = " + this.getShopId());
		// Excel检测
		this.message = ExcelUtils.checkExcel(excel, rowIndex, rules);
		if (message != null) {
			this.success = false;
			return "uploadJSON";
		}
		String filePath = this.saveExcel(excel);
		if (filePath != null) {
			try {
				Map<Integer, String> execelTitleMap = ExcelUtils.readExcelTitle(excel, rowIndex);
				String hql = null;
				ETL_Column column = null;

				for (Entry<Integer, String> entry : execelTitleMap.entrySet()) {
					// TODO 只能是模板文件来生成，如果已经存在就不能更新
					hql = "from ETL_Column where column_name='" + entry.getValue() + "' and shop_id=" + this.getShopId();
					column = columnService.find(hql);
					if (column == null) {
						column = new ETL_Column();
						column.setShop_id(this.getShopId());
						column.setColumn_cellIndex(entry.getKey());
						column.setColumn_name(entry.getValue());
						columnService.save(column);
					}
					/*
					else {
						column.setColumn_cellIndex(entry.getKey());
						columnService.alter(column);
					}
					*/
				}

				ETL_Excel excle = new ETL_Excel();
				excle.setExcel_name(excelFileName);
				excle.setExcel_filePath(filePath);
				excle.setExcel_rowIndex(rowIndex);
				excle.setStatus(0);
				excle.setShop_id(this.getShopId());
				excle.setUser_id(this.getUserID());
				
				excelService.save(excle);

			} catch (InvalidFormatException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		this.message = "上传成功";
		this.success = true;
		return "uploadJSON";
	}

	public String update() {
		return "uploadJSON";
	}

	public void prepareUpdate() {
		ETL_Excel excel = excelService.findById(excelId);
		excel.setExcel_rowIndex(rowIndex);
		excelService.alter(excel);
		this.success = true;
	}

	@JSON(name = "rows")
	public List<ExcelVO> getDatas() {
		return datas;
	}

	public void setExcelId(Integer excelId) {
		this.excelId = excelId;
	}

	public void setDatas(List<ExcelVO> datas) {
		this.datas = datas;
	}

	
	public void setExcel(File excel) {
		this.excel = excel;
	}

	public void setExcelContentType(String excelContentType) {
		this.excelContentType = excelContentType;
	}

	public void setExcelFileName(String excelFileName) {
		this.excelFileName = excelFileName;
	}

	public Boolean getSuccess() {
		return success;
	}

	public String getMessage() {
		return message;
	}

	private String saveExcel(File srcFile) {
		String rootPath = PropertiesUtils.getProperty("upload.file.path");
		if (!rootPath.endsWith("/")) {
			rootPath = rootPath + "/";
		}
		String filePath = rootPath + this.getShopId() + "/";

		File file = new File(filePath);
		if (!file.exists()) {
			file.mkdirs();
		}
		
		String extension = excelFileName.substring(excelFileName.lastIndexOf("."));
		String shopFilePath = filePath + DateUtils.format(new Date(), DateUtils.PARENT_SIMPLE_ALL) + extension;
		File destFile = new File(shopFilePath);
		try {
			FileUtils.copyFile(srcFile, destFile);
			return shopFilePath;
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;
	}

	public String getExcelFileName() {
		String fileName = null;
		try {
			fileName = new String(this.excelFileName.getBytes("GBK"), "ISO-8859-1");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		return fileName;
	}

	public void setRowIndex(Integer rowIndex) {
		this.rowIndex = rowIndex;
	}

	public void setExcelName(String excelName) {
		this.excelName = excelName;
	}

	public Integer getRowIndex() {
		return rowIndex;
	}
	
	private Integer total = 0;

	public Integer getTotal() {
		return total;
	}
}
